package com.b2c.repository.tui;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.b2c.entity.result.PagingResponse;
import com.b2c.common.utils.DateUtil;
import com.b2c.entity.erp.ErpGoodsSpecEntity;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.tui.OrderSendReturnEntity;
import com.b2c.entity.enums.erp.EnumOrderReturnStatus;
import com.b2c.repository.Tables;

@Repository
public class OrderSendReturnRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询总页数
     *
     * @return
     */
    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     * @param pageIndex
     * @param pageSize
     * @return
     */
    @Transactional
    public PagingResponse<OrderSendReturnEntity> getList(int pageIndex, int pageSize, String orderNum,String logisticsCode,Integer status) {
        String sql = "SELECT SQL_CALC_FOUND_ROWS o.*,sh.name as shopName,"
                + "g.`name` as goodsName,CONCAT(gs.color_value,gs.size_value) as goodsSpec,gs.color_image as goodsImg,sh.type as shopType " 
                + " FROM erp_order_send_return as o "
                + " LEFT JOIN " + Tables.DcShop + " as sh on sh.id= o.shopId "
                + " LEFT JOIN " + Tables.ErpGoods + " as g on g.id= o.goodsId "
                + " LEFT JOIN " + Tables.ErpGoodsSpec + " as gs on gs.id= o.goodsSpecId "
                + " WHERE 1=1 ";

        List<Object> params = new ArrayList<>();

        if (StringUtils.isEmpty(orderNum) == false) {
            sql += " AND o.orderSn = ? ";
            params.add(orderNum);
        }

        if (StringUtils.isEmpty(logisticsCode) == false) {
            sql += " AND o.logisticsCode=?";
            params.add(logisticsCode);
        }
        if (status == null){
            //没选状态就默认已收货未结算的
            sql += " AND o.status=2 AND o.isSettle=0 ";
        }

        if (status != null && status > -1) {
            sql += " AND o.status=?";
            params.add(status);
        }

        sql += " ORDER BY o.id DESC LIMIT ?,?";
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(OrderSendReturnEntity.class),
                params.toArray(new Object[params.size()]));
        var totalSize = getTotalSize();
      
        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    public List<OrderSendReturnEntity> getListByIds(String ids) {
        String sql = "SELECT SQL_CALC_FOUND_ROWS o.*,sh.name as shopName,g.number as goodsNumber,"
        + "g.`name` as goodsName,CONCAT(gs.color_value,gs.style_value,gs.size_value) as goodsSpec,gs.color_image as goodsImg,gs.purPrice as goodsPrice " 
        + " FROM erp_order_send_return as o "
        + " LEFT JOIN " + Tables.DcShop + " as sh on sh.id= o.shopId "
        + " LEFT JOIN " + Tables.ErpGoods + " as g on g.id= o.goodsId "
        + " LEFT JOIN " + Tables.ErpGoodsSpec + " as gs on gs.id= o.goodsSpecId "
        + " WHERE o.id in ("+ids+") ";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(OrderSendReturnEntity.class));
    }


    public ResultVo<Integer> remark(Long id, String remark) {
        jdbcTemplate.update("UPDATE erp_order_send_return SET remark =? WHERE id=?",remark,id);
        return new ResultVo<>(EnumResultVo.SUCCESS);
    }

     /**
     * 退回商品计损
     *
     * @param goodsIds
     * @param specNumber
     * @param quantities
     */
    @Transactional
    public ResultVo<Integer> returnOrderStockBadIn(Long returnId,Integer quantity, String billNo) {
        if (returnId==null || quantity==null || quantity<= 0)
        return new ResultVo<>(EnumResultVo.ParamsError,"参数错误");
        //查询退货数据
        OrderSendReturnEntity returnOrder = null;
        try {
            returnOrder = jdbcTemplate.queryForObject("select * from erp_order_send_return where id=?", new BeanPropertyRowMapper<>(OrderSendReturnEntity.class),returnId);
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.DataExist,"参数错误,退货单不存在");
        }
        final OrderSendReturnEntity finalReturnOrder = returnOrder;
    
        Integer locationId = 0;
        var spec = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE id=?",
                new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), returnOrder.getGoodsSpecId());
        double lossAmount = spec.getPurPrice() * quantity;
        
        // try {
        //     // 查询不良品仓是否存在该商品
        //     var badStock = jdbcTemplate.queryForObject(
        //             "SELECT * FROM " + Tables.ErpGoodsBadStock + " WHERE specId=? AND locationId=?",
        //             new BeanPropertyRowMapper<>(ErpGoodsBadStockEntity.class), specId, locationId);
        //     badStockId = badStock.getId();
        //     // 有记录，update
        //     jdbcTemplate.update("UPDATE " + Tables.ErpGoodsBadStock + " set currentQty=currentQty+? WHERE id=?",
        //             quantities, badStock.getId());
        // } catch (Exception e) {
            String sql = "INSERT INTO " + Tables.ErpGoodsBadStock
                    + " SET quantity=?,type=1 ,specNumber=? ,locationId=? ,goodsId=? ,specId=?,sourceId=?,lossAmount=?,result=?,reason=?,status=1,resultTime=?";
            KeyHolder holder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                    ps.setLong(1, quantity);
                    ps.setString(2, finalReturnOrder.getGoodsSpecNumber());
                    ps.setLong(3, locationId);
                    ps.setInt(4, finalReturnOrder.getGoodsId());
                    ps.setInt(5, finalReturnOrder.getGoodsSpecId());
                    ps.setString(6, returnId.toString());
                    ps.setDouble(7, lossAmount);
                    ps.setString(8, "计入损失");
                    ps.setString(9, "货物损坏，供应商拒收，消费者退款成功");
                    ps.setString(10, DateUtil.getCurrentDateTime());
                    return ps;
                }
            }, holder);
            Long badStockId = holder.getKey().longValue();
        // }

        // 日志
        String remark = "退货不良品入库SKU :" +  finalReturnOrder.getGoodsSpecNumber() + "退货单号:" + billNo;
        jdbcTemplate.update(
                "INSERT INTO " + Tables.ErpGoodsBadStockLog + " SET "
                        + "stockId=?,specId=?,locationId=?,quantity=?,type=1,remark=?",
                badStockId, finalReturnOrder.getGoodsSpecId(), locationId, quantity, remark);

        String remark1 ="";
        if(StringUtils.hasText(finalReturnOrder.getRemark())) remark1 = finalReturnOrder.getRemark()+"【计入损失】";
        else remark1 = "计入损失";
        // 更新退货订单明细已入库数量
        jdbcTemplate.update("UPDATE erp_order_send_return SET badQuantity=badQuantity+?,remark=? where id=? ",quantity,remark1, returnId);

        // 判读已入库数量，数量相等则更新状态
        Long current1 = jdbcTemplate.queryForObject(
                "SELECT SUM(quantity) FROM erp_order_send_return WHERE id=?", long.class, returnId);
        Long current2 = jdbcTemplate.queryForObject(
                "SELECT SUM(inQuantity) FROM erp_order_send_return WHERE id=?", long.class, returnId);
        Long current3 = jdbcTemplate.queryForObject(
                "SELECT SUM(badQuantity) FROM erp_order_send_return WHERE id=?", long.class, returnId);
        if (current1 == current2 + current3)
            jdbcTemplate.update("UPDATE erp_order_send_return SET status=? WHERE id=?",
                    EnumOrderReturnStatus.Bad.getIndex(), returnId);
        
        return new ResultVo<>(EnumResultVo.SUCCESS,"SUCCESS");
    }

    public ResultVo<Integer> returnOrderHuanhuoSend(Long returnId, String logisticsCode) {
        String sql = "UPDATE erp_order_send_return SET exchangeLogisticsCode=?,`status`=? WHERE id=?";
        jdbcTemplate.update(sql, logisticsCode,EnumOrderReturnStatus.ExchangeComplete.getIndex(),returnId);
        return new ResultVo<>(EnumResultVo.SUCCESS,"SUCCESS");
    }
}
